package com.community.sqlapp.controller;

import com.community.sqlapp.service.DatabaseService;
import com.community.sqlapp.service.QianwenToSqlService;
import com.community.sqlapp.databaseConfiguration.TargetDataSource;
import lombok.RequiredArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;

import java.util.*;

@RequiredArgsConstructor
@RestController
@RequestMapping("/api/db")
public class SqlController {
    
    private final DatabaseService databaseService;
    private final QianwenToSqlService qianwenService;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    // 获取所有数据库列表
    @GetMapping("/dataTables")
    @TargetDataSource("dynamic")
    public List<String> getDataBases() {
        return jdbcTemplate.queryForList("SHOW DATABASES", String.class);
    }
    /**
     * 切换库查询表
     * @param
     * @return
     */
    @PostMapping("/tables")
    public List<String> getTables(@RequestParam String dbName) {
        // 安全校验：仅允许字母数字下划线的数据库名（根据实际需求调整）
        if (!dbName.matches("^[a-zA-Z0-9_]+$")) {
            throw new IllegalArgumentException("非法数据库名");
        }
        // 切换数据库
        jdbcTemplate.execute("USE " + dbName);
        // 获取当前数据库表列表
        return jdbcTemplate.queryForList("SHOW TABLES", String.class);
    }

    /**
     * 获取表结构
     * @param tableName
     * @return
     */
    @GetMapping("/table/{tableName}")
    @ResponseBody
    public List<Map<String, Object>> getTableStructure(@PathVariable String tableName) {
        System.out.println(databaseService.getTableStructure(tableName).get(0));
        return databaseService.getTableStructure(tableName);

    }
    /**
     * 执行sql
     * @param request
     * @return
     */
    @PostMapping("/execute")
    @ResponseBody
    public Map<String, Object> executeQuery(@RequestBody Map<String, String> request) {
        String sql = request.get("sql");
        Map<String, Object> result = new HashMap<>(); // 初始化result
        try {
            if (!databaseService.isConnectionValid()) {
                result.put("success", false);
                result.put("error", "数据库连接已断开，请重新连接");
                return result;
            }
            return databaseService.executeQuery(sql);
        } catch (Exception e) {
            result.put("success", false);
            result.put("error", "执行SQL失败: " + e.getMessage());
            return result;
        }
    }

    /**
     * @param request
     * 通过AI大模型生成sql
     * @return
     */
    @PostMapping("/generate")
    @ResponseBody
    public Map<String, String> generateSql(@RequestBody Map<String, Object> request) {
        Map<String, String> response = new HashMap<>();
        try {
            String prompt = (String) request.get("prompt");
            @SuppressWarnings("unchecked")
            Map<String, List<Map<String, Object>>> selectedTablesInfo = (Map<String, List<Map<String, Object>>>) request.get("selectedTablesInfo");

            // 处理selectedTablesInfo为空的情况
            if (selectedTablesInfo == null || selectedTablesInfo.isEmpty()) {
                response.put("sql", "-- 未提供表信息，无法生成SQL");
                return response;
            }

            // 调用API生成SQL（直接使用前端传递的表结构）
            String generatedSql = qianwenService.generateSql(prompt, selectedTablesInfo);
            response.put("sql", generatedSql);
        } catch (Exception e) {
            e.printStackTrace();
            response.put("sql", "-- 生成SQL时出错: " + e.getMessage() + "\n请检查表信息列表格式。");
        }
        return response;
    }

    /**
     * 查询结果返回
     * @return
     */
    @GetMapping("/show/tables")
    @ResponseBody
    public List<String> getTables() {
        try {
            //return databaseService.getAllTables();
            return jdbcTemplate.queryForList("SHOW TABLES", String.class);
        } catch (Exception e) {
            return new ArrayList<>();
        }
    }
    @GetMapping("/test-connection")
    public String testConnection() {
        try {
            jdbcTemplate.execute("SELECT 1");
            return "Database connection successful!";
        } catch (Exception e) {
            return "Database connection failed: " + e.getMessage();
        }
    }
}